﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.ComponentModel;
using System.Text;

namespace UniBasel.Biozentrum.NMR
{
	[DataObjectAttribute]
	public static class NMRExperiments
	{
		private static readonly string[] _ExperimentSearchFields = null;
		private static readonly string[] _PulseProgramSearchFields = null;
		private static readonly string[] _WaveShapeSearchFields = null;
		private static readonly string[] _CPDSearchFields = null;
		private static readonly string[] _ScriptSearchFields = null;

		[DataObjectMethodAttribute(DataObjectMethodType.Select, true)]
		public static DataView SELECT(string SearchString, Constants.DBTables table)
		{
			DataView result;
			result = getData(table);

			result.RowFilter = getRowFilter(SearchString, table);
			return result;
		}

		/// <summary>
		/// Parsing of the parameter to get the Filter string applicable for DataView.RowFilter
		/// </summary>
		/// <param name="searchString">A search string</param>
		/// <param name="table">A table</param>
		private static string getRowFilter(string searchString, Constants.DBTables table)
		{
			if (searchString == null) return "";
			if (searchString.Trim().Length == 0) return "";

			string[] terms = searchString.Split(' '); // Array Terms contains single words from searchString

			string[] searchFields;

			// In which colums the search is performed is according to the table the data is received from
			switch (table)
			{
				case Constants.DBTables.Experiments:
					searchFields = _ExperimentSearchFields;
					break;
				case Constants.DBTables.PulsePrograms:
					searchFields = _PulseProgramSearchFields;
					break;
				case Constants.DBTables.WaveShapes:
					searchFields = _WaveShapeSearchFields;
					break;
				case Constants.DBTables.CPDs:
					searchFields = _CPDSearchFields;
					break;
				case Constants.DBTables.Scripts:
					searchFields = _ScriptSearchFields;
					break;
				default:
					throw new NotImplementedException(); // NEXT update for all tables
			}

			StringBuilder finalResult = new StringBuilder();
			string modifier = "", connector = "OR ", mTerm;

			// For search term to include (when no '-' sign is in front of it)
			// (<column1> LIKE '*<term>*') OR ... OR (<columnN> LIKE '*<term>*') for string type columns
			// and (<column> = '<term>') for numeric
			// Means at least one column is like search term
			// For search term to be excluded ( when '-' sign is in front)
			// '=' sing is changed to '<>', 'NOT' is added in front of 'LIKE'  and 'OR' changed to 'AND'
			foreach (string term in terms)
			{
				if (term == "") continue;
				modifier = "";
				connector = "OR ";   // Extra space is important for length must be 3, like in "AND"
				mTerm = term;
				if (term[0] == '-')
				{
					modifier = "NOT";
					connector = "AND";
					mTerm = term.Substring(1);
				}

				finalResult.Append("(");
				try
				{
					Int32.Parse(mTerm);	// Check if term is numeric, then Id can also be compared, otherwise Id is skiped
					finalResult.AppendFormat("Id{2}'{0}' {1} ", mTerm, connector, (modifier == "NOT") ? "<>" : "=");
				}
				catch
				{

				}
				for (int i = 1; i < searchFields.Length; i++)
				{

					finalResult.AppendFormat("{0} {2} LIKE '*{1}*'", searchFields[i], mTerm, modifier);
					finalResult.AppendFormat(" {0} ", connector);
				}
				finalResult.Remove(finalResult.Length - 5, 5); // Removes last Connector
				finalResult.Append(") AND ");
			}
			finalResult.Remove(finalResult.Length - 4, 4);

			return finalResult.ToString();
		}

		private static DataView getData(Constants.DBTables table)
		{
			string queryText;
			string tableName = table.ToString();

			switch (table)
			{
				case Constants.DBTables.Experiments:
					queryText =
@"SELECT        
	id AS Id, 
	name AS Name, 
	[type] AS [Type], 
	date AS Date, 
	dim AS Dimentionality, 
	memo AS Memo, 
	dbo.ExpConcatAuthNames(id) AS Authors
FROM 
	experiments AS e";
					break;
				case Constants.DBTables.PulsePrograms:
					queryText =
@"SELECT 
	id AS Id, 
	name AS Name, 
	date AS Date, 
	memo AS Memo, 
	dbo.PPConcatAuthNames(id) AS Authors 
FROM 
	pulseprograms AS p";
					break;
				case Constants.DBTables.WaveShapes:
					queryText =
@"SELECT 
	id AS Id, 
	name AS Name, 
	date AS Date, 
	memo AS Memo, 
	dbo.wsConcatAuthNames(id) AS Authors 
FROM 
	waveshapes AS w";
					break;
				case Constants.DBTables.CPDs:
					queryText =
@"SELECT 
	id AS Id, 
	name AS Name, 
	date AS Date, 
	memo AS Memo, 
	dbo.cpdConcatAuthNames(id) AS Authors 
FROM 
	cpds AS w";
					break;
				case Constants.DBTables.Scripts:
					queryText =
@"SELECT 
	id AS Id, 
	name AS Name, 
	type AS Type, 
	date AS Date, 
	memo AS Memo, 
	dbo.scrConcatAuthNames(id) AS Authors 
FROM 
	scripts AS p";
					break;
				default:
					throw new NotImplementedException();
			}

			DataView finalResult;

			using (SqlConnection NMRExperiments = new SqlConnection(Constants.NMRExperimentsConStr))
			{
				NMRExperiments.Open();

				SqlCommand querySelect = NMRExperiments.CreateCommand();
				querySelect.CommandText = queryText;

				SqlDataAdapter executingQuery = new SqlDataAdapter(querySelect);

				DataSet queryResult = new DataSet();
				executingQuery.Fill(queryResult, tableName);

				DataTable finalTable = queryResult.Tables[tableName];
				finalResult = new DataView(finalTable);
			}
			return finalResult;
		}

		static NMRExperiments()
		{
			_ExperimentSearchFields = new string[] { "Id", "Name", "Type", "Dimentionality", "Memo", "Authors" };
			_PulseProgramSearchFields = new string[] { "Id", "Name", "Memo", "Authors" };
			_WaveShapeSearchFields = new string[] { "Id", "Name", "Memo", "Authors" };
			_CPDSearchFields = new string[] { "Id", "Name", "Memo", "Authors" };
			_ScriptSearchFields = new string[] { "Id", "Name", "Type", "Memo", "Authors" };
		}
	}
}